﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using Follow.Utils;
using Follow.Model;
using System.Transactions;
using Follow.DataAccess;
using Follow.Common;

namespace Follow.DataAccess
{
    public class ContatoDAO
    {
        private SqlDataReader reader = null;
        private SqlConnection conn = null;
        private SqlCommand cmd = null;

        public ContatoDAO()
        {
            conn = ConnectionUtils.getConnection();
        }

        public IList<Contato> findAll()
        {
            IList<Contato> contato = new List<Contato>();

            try
            {
                conn.Open();

                String sqlCommand = " select * from contato order by nome ";
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Contato contact = new Contato();
                    contact.IDContato = reader.GetInt32(0);
                    contact.IDUsuario = reader.GetInt32(1);
                    contact.IDCliente = reader.GetInt32(2);
                    contact.Nome = reader.GetString(3);
                    contact.Dpto = reader.GetString(4);
                    contact.Email = reader.GetString(5);
                    contact.Tel1 = reader.GetString(6);
                    contact.Tel2 = reader.GetString(7);
                    contato.Add(contact);
                }
            }

            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            return contato;
        }


        public void insert(Contato contato, Int32 idlogin)
        {
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    String sqlCommand = " insert into contato (usuario_idusuario, cliente_idcliente, nome," +
                                        " dpto, email, tel1, tel2) values (@idusuario, @idcliente, @nome," +
                                        " @dpto, @email, @tel1, @tel2);";
                    cmd = new SqlCommand(sqlCommand, conn);
                    if (contato.IDUsuario == Int32.MinValue)
                    {
                        cmd.Parameters.Add(new SqlParameter("idusuario", DBNull.Value));
                    }
                    else
                    {
                        cmd.Parameters.Add(new SqlParameter("idusuario", contato.IDUsuario));
                    }
                    cmd.Parameters.Add(new SqlParameter("idcliente", contato.IDCliente));
                    cmd.Parameters.Add(new SqlParameter("nome", contato.Nome));
                    cmd.Parameters.Add(new SqlParameter("dpto", contato.Dpto));
                    cmd.Parameters.Add(new SqlParameter("email", contato.Email));
                    cmd.Parameters.Add(new SqlParameter("tel1", contato.Tel1));
                    cmd.Parameters.Add(new SqlParameter("tel2", contato.Tel2));
                    cmd.ExecuteNonQuery();
                    transaction.Complete();
                }
                catch (Exception)
                {
                    transaction.Dispose();
                    throw;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }

                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
                LogDAO log = new LogDAO();
                log.insert(idlogin, SQLStringSingleton.GetCommandText(cmd), "Insert", "Contato");
            }
        }

        public void update(Contato contato, Int32 idlogin)
        {
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    String sqlCommand = " update contato set usuario_idusuario = @idusuario, cliente_idcliente =" +
                                        " @idcliente, nome = @nome, dpto = @dpto, email = @email, tel1 = @tel1," +
                                        " tel2 = @tel2 where idcontato = @idcontato;";
                    cmd = new SqlCommand(sqlCommand, conn);
                    if (contato.IDUsuario == Int32.MinValue)
                    {
                        cmd.Parameters.Add(new SqlParameter("idusuario", DBNull.Value));
                    }
                    else
                    {
                        cmd.Parameters.Add(new SqlParameter("idusuario", contato.IDUsuario));
                    }
                    cmd.Parameters.Add(new SqlParameter("idcontato", contato.IDContato));
                    cmd.Parameters.Add(new SqlParameter("idcliente", contato.IDCliente));
                    cmd.Parameters.Add(new SqlParameter("nome", contato.Nome));
                    cmd.Parameters.Add(new SqlParameter("dpto", contato.Dpto));
                    cmd.Parameters.Add(new SqlParameter("email", contato.Email));
                    cmd.Parameters.Add(new SqlParameter("tel1", contato.Tel1));
                    cmd.Parameters.Add(new SqlParameter("tel2", contato.Tel2));
                    cmd.ExecuteNonQuery();
                    transaction.Complete();
                }
                catch (Exception)
                {
                    transaction.Dispose();
                    throw;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }

                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
                LogDAO log = new LogDAO();
                log.insert(idlogin, SQLStringSingleton.GetCommandText(cmd), "Update", "Contato");
            }
        }

        public void delete(Int32 id, Int32 idlogin)
        {
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    String sqlCommand = " delete from contato where idcontato = @id;";
                    cmd = new SqlCommand(sqlCommand, conn);
                    cmd.Parameters.Add(new SqlParameter("id", id));
                    cmd.ExecuteNonQuery();
                    transaction.Complete();
                }
                catch (Exception ex)
                {
                    transaction.Dispose();
                    throw ex;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }

                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
                LogDAO log = new LogDAO();
                log.insert(idlogin, SQLStringSingleton.GetCommandText(cmd), "Delete", "Contato");
            }
        }


        public Contato findById(Int32 id)
        {
            Contato contact = new Contato();
            try
            {
                conn.Open();
                String sqlCommand = " select * from contato where idcontato = @id;";
                cmd = new SqlCommand(sqlCommand, conn);
                cmd.Parameters.Add(new SqlParameter("id", id));
                reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    contact.IDContato = reader.GetInt32(0);
                    contact.IDUsuario = (reader.IsDBNull(1)) ? Int32.MinValue : reader.GetInt32(1);
                    contact.IDCliente = reader.GetInt32(2);
                    contact.Nome = reader.GetString(3);
                    contact.Dpto = reader.GetString(4);
                    contact.Email = reader.GetString(5);
                    contact.Tel1 = reader.GetString(6);
                    contact.Tel2 = (reader.IsDBNull(7)) ? null : reader.GetString(7);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return contact;
        }

        public IList<Contato> findByCliente(Int32 id)
        {

            IList<Contato> contatos = new List<Contato>();
            try
            {
                conn.Open();
                String sqlCommand = " select c.*, u.login from contato c left join" +
                                    " usuario u on c.usuario_idusuario = u.idusuario" +
                                    " where cliente_idcliente = @id;";
                cmd = new SqlCommand(sqlCommand, conn);
                cmd.Parameters.Add(new SqlParameter("id", id));
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Contato c = new Contato();
                    c.IDContato = reader.GetInt32(0);
                    c.IDUsuario = (reader.IsDBNull(1)) ? Int32.MinValue : reader.GetInt32(1);
                    c.IDCliente = reader.GetInt32(2);
                    c.Nome = reader.GetString(3);
                    c.Dpto = reader.GetString(4);
                    c.Email = reader.GetString(5);
                    c.Tel1 = reader.GetString(6);
                    c.Tel2 = (reader.IsDBNull(7)) ? null : reader.GetString(7);
                    c.NomeUsuario = (reader.IsDBNull(8)) ? null : reader.GetString(8);
                    contatos.Add(c);
                }
            }

            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            return contatos;
        }

        public Contato findByIdUsuarioContato(Int32 id)
        {
            Contato contact = null;
            try
            {
                conn.Open();
                String sqlCommand = " select * from contato where usuario_idusuario = " + id;
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    contact = new Contato();
                    contact.IDContato = reader.GetInt32(0);
                    contact.IDUsuario = reader.GetInt32(1);
                    contact.IDCliente = reader.GetInt32(2);
                    contact.Nome = reader.GetString(3);
                    contact.Dpto = reader.GetString(4);
                    contact.Email = reader.GetString(5);
                    contact.Tel1 = reader.GetString(6);
                    contact.Tel2 = reader.GetString(7);

                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            return contact;
        }

     }
}